library(ggplot2)
library(reshape)
library(gridExtra)

sqLiteConnect <- function(database, table) {
  require(DBI)
  con <- dbConnect(RSQLite::SQLite(), dbname = database)
  query <- dbSendQuery(con, paste("SELECT * FROM ", table, ";", sep="")) 
  result <- fetch(query, n = -1)
  dbClearResult(query)
  dbDisconnect(con)
  return(result)
}

db <- "m5s_newsmedia_hits_mar2016.sqlite"
corriere_berlusconi <- sqLiteConnect(db, "corriere_berlusconi")
names(corriere_berlusconi)[6] <- "cor_berlusconi"
corriere_beppe <- sqLiteConnect(db, "corriere_beppe")
names(corriere_beppe)[6] <- "cor_grillo"
corriere_politica <- sqLiteConnect(db, "corriere_politica")
names(corriere_politica)[6] <- "cor_pol"
corriere_PresCon <- sqLiteConnect(db, "corriere_PresCon")
names(corriere_PresCon)[6] <- "cor_PresCon"

repubblica_berlusconi <- sqLiteConnect(db, "repubblica_berlusconi")
names(repubblica_berlusconi)[6] <- "rep_berlusconi"
repubblica_beppe <- sqLiteConnect(db, "repubblica_beppe")
names(repubblica_beppe)[6] <- "rep_grillo"
repubblica_politica <- sqLiteConnect(db, "repubblica_politica")
names(repubblica_politica)[6] <- "rep_pol"
repubblica_PresCon <- sqLiteConnect(db, "repubblica_PresCon")
names(repubblica_PresCon)[6] <- "rep_PresCon"

df <- cbind(corriere_berlusconi[,c('from_date','to_date','cor_berlusconi')],
            'cor_grillo' = corriere_beppe[,'cor_grillo'],
            cor_pol = corriere_politica[,'cor_pol'],
            rep_berlusconi = repubblica_berlusconi[,'rep_berlusconi'],
            rep_grillo = repubblica_beppe[,'rep_grillo'],
            rep_pol = repubblica_politica[,'rep_pol'])
  
election1994 <- as.Date('1994-03-28') # row 117
election2013 <- as.Date('2013-02-25') # row 1104

week_window = 35
df_berlusconi <- df[(117-week_window):(117+week_window),c("from_date","to_date","cor_pol","cor_berlusconi","rep_pol","rep_berlusconi")]
df_berlusconi$n_week <- -week_window:week_window
df_grillo <- df[(1104-week_window):(1104+week_window),c("from_date","to_date","cor_pol","cor_grillo","rep_pol","rep_grillo")]
df_grillo$n_week <- -week_window:week_window

df_berlusconi_grillo <-
  data.frame(subject = c(rep('Berlusconi',nrow(df_berlusconi)*2), rep('Grillo',nrow(df_grillo)*2)),
             newspaper = rep(c(rep('Corriere', nrow(df_berlusconi)), rep('Repubblica', nrow(df_berlusconi))),2),
             from_date = c(rep(df_berlusconi$from_date, 2), rep(df_grillo$from_date, 2)),
             n_week = c(rep(df_berlusconi$n_week, 2), rep(df_grillo$n_week, 2)),
             pol_ratio = c(df_berlusconi$cor_berlusconi / df_berlusconi$cor_pol,
                           df_berlusconi$rep_berlusconi / df_berlusconi$rep_pol,
                           df_grillo$cor_grillo / df_grillo$cor_pol,
                           df_grillo$rep_grillo / df_grillo$rep_pol),
             stringsAsFactors = TRUE)
             
            

plot(ggplot(df_berlusconi_grillo) +
  geom_line(aes(x=n_week, y=pol_ratio, colour=subject, linetype = newspaper)) + 
  scale_colour_manual(name=NULL, values=c('#377eb8', '#e41a1c')) +
  geom_point(data=subset(df_berlusconi_grillo, 
                         subject == "Berlusconi" & n_week %in% c(-18)), 
             aes(x=n_week, y=pol_ratio), shape = 1) +
  guides(color=FALSE) +
  theme(legend.position = 'bottom') +
  annotate("text", x = 25, y = 1, label = "Berlusconi", colour = '#377eb8') +
  labs(x='Weeks to/from election', y='Fraction of political articles') +
  annotate("text", x = 25, y = 0.2, label = "Grillo", colour = '#e41a1c') +
  annotate("text", x=-25, y=0.75, label="Berlusconi announces\nhis open to\nthe possibility of\nrunning for office") +
  geom_segment(y=0.55, x=-22, yend=0.15, xend=-18.5,
               arrow=arrow(length=unit(2, "mm")), alpha=.2, colour="gray"))
  
  